package it.scscomputers.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

/**
 * Compare the schema of two database (of the same vendor) instances in order to verify
 * that they are equal. <br>
 * Tipical use case is: compare two reviosion of the same database
 * to catch differences. <br>
 * Example: compare the last version of a schema with the schema obtained
 * from the migration of an existing schema. 
 * <p>
 * 
 * Dabase vendor supported: MySQL, Oracle.<br>
 * Tested on MySQL 5.1 and Oracle Database 10g Express Edition.
 * <br><br>
 * 
 * The tool check:
 * <ul>
 * <li>Tables
 * <li>Columns: name, type, nullable, default value
 * <li>Primary keys
 * <li>Foreign keys
 * <li>Sequences: only for Oracle.
 * <li>Stored procedures and functions: procedure name. Only for MySQL.
 * </ul>
 * <p> 
 * 
 * Usage example (ddd jdbc driver to classpath):<br>
 * 
 * java CompareSchema ORA localhost 1521 XE AUSYLIA_TELLA mysqladmin <br> 
 *      localhost 1521 XE AUSYLIA_TELLA2 mysqladmin 
 * <p>
 * 
 * Notes:<br>
 * 
 * In Oracle there isn't a secure way to obtain a procedure name, so check of
 * stored procedure has not been implemented.
 * The query to use to obtain this information should be:<br>
 * <code>select * from USER_SOURCE WHERE line = 1</code>
 * 
 * 
 * <pre>
 * $Date: 2012-02-15 14:56:14 +0000 (Wed, 15 Feb 2012) $
 * </pre>
 * 
 * @author $Author: cverdecchia@gmail.com $
 * @version $Revision: 3 $
 */
public class CompareSchema {
	
	// Parametri di lancio del main
//	private static String[] args = null;
	
//	private static boolean VALUTAZIONE = true;
//	private static String MSG = "";
	
	// Connessioni ai 2 db
//	private static Connection CON1 = null;
//	private static Connection CON2 = null;

	/**
	 * Stampa info per utilizzo del programma.
	 */
	private static void usage() {
		System.out.println("CompareSchema usage:");
		System.out
				.println("\tjava CompareSchema DB_vendor (ORA,MySQL) "
						+ "host1 port1 dbname1 (for oracle db type, eg. ora9a, XE, etc.)  username1 pwd1 "
						+ "host2 port2 dbname2 (for oracle db type, eg. ora9a, XE, etc.)  username2 pwd2");
		System.exit(1);
	}

	public static void main(String[] args) {
		
//		CompareSchema.args = args;


		String MSG = "";
//		VALUTAZIONE = true;

		try {

			if (args.length < 11) {
				usage();
			} else if (args[0].isEmpty()) {
				usage();
			} else if (args[1].isEmpty()) {
				usage();
			} else if (args[2].isEmpty()) {
				usage();
			} else if (args[3].isEmpty()) {
				usage();
			} else if (args[4].isEmpty()) {
				usage();
			} else if (args[5].isEmpty()) {
				usage();
			} else if (args[6].isEmpty()) {
				usage();
			} else {


				/*
				 * Oracle
				 */
				if (args[0].equalsIgnoreCase("ORA")) {
					MSG = CompareSchema.confrontaOracle(args);
					
				/*
				 * MySQL
				 */
				} else {
					MSG = CompareSchema.confrontaMySQL(args);
				}
			}
			
//			if (MSG.isEmpty()) {
//				System.out.println("Gli schemi sono uguali");
//				System.exit(1);
//			} else {
//				System.out.println(MSG);
//				System.exit(1);
//			}
			
		} catch (Exception e) {
			MSG = e.getMessage();
			System.err.println("Exception: " + e);
			e.printStackTrace();
		} 
		
		if (MSG.isEmpty()) {
			System.out.println("Schemas are equals!");
			System.exit(0);
		} else {
			System.out.println(MSG);
			System.exit(1);
		}
	}
	
	/**
	 * @return
	 * @throws Exception
	 */
	public static String confrontaOracle(String[] args) throws Exception {
		
		String msg = "";
		
		Connection CON1 = null;
		Connection CON2 = null;
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			CON1 = DriverManager.getConnection("jdbc:oracle:thin:@"
					+ args[1] + ":" + args[2] + ":" + args[3], args[4],
					args[5]);
			CON2 = DriverManager.getConnection("jdbc:oracle:thin:@"
					+ args[6] + ":" + args[7] + ":" + args[8], args[9],
					args[10]);

			String selTabelle = "select TABLE_NAME from USER_TABLES order by TABLE_NAME";
			String selColonne = "select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT from USER_TAB_COLUMNS where TABLE_NAME = '";
//		selnColonne = "select count(*) from USER_TAB_COLUMNS where TABLE_NAME = '";

			System.out.println("Engine: "  + args[0] + " connections opened....");

			// setto autocommit
			CON1.setAutoCommit(true);
			CON2.setAutoCommit(true);

			// Confronta le tabelle
			Statement seltabs1 = CON1.createStatement();
			Statement seltabs2 = CON2.createStatement();
			ResultSet rstabs1 = seltabs1.executeQuery(selTabelle);
			ResultSet rstabs2 = seltabs2.executeQuery(selTabelle);

			// Scorro le tabelle dello schema
			while (rstabs1.next()) {

				// Avanzo sul secondo rs
				rstabs2.next();

				String nmtab1 = rstabs1.getString(1);
				String nmtab2 = rstabs2.getString(1);

				if (!nmtab1.equals(nmtab2)) {
					msg = "Expected " + nmtab1
							+ " on SCHEMA1 found instead on SCHEMA2 "
							+ nmtab2;
//				VALUTAZIONE = false;
					break;
				}

				// Confronta le colonne di ciascuna tabella
				msg = CompareSchema.confrontaColonne(CON1, CON2, "colonne", 
						selColonne + nmtab1 + "' order by COLUMN_NAME", nmtab1, 
						selColonne + nmtab2 + "' order by COLUMN_NAME", nmtab2);
				
				if (msg.isEmpty()) {
					// Confronta PK di ciascuna tabella
					msg =  CompareSchema.confrontaColonne(CON1, CON2, "PK",
							"SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME = '" + nmtab1 + "' AND C.CONSTRAINT_TYPE = 'P' order by A.POSITION", nmtab1, 
							"SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME = '" + nmtab2 + "' AND C.CONSTRAINT_TYPE = 'P' order by A.POSITION", nmtab2);
				} else break;

				if (msg.isEmpty()) {
					// Confronta FK di ciascuna tabella
					msg = CompareSchema.confrontaColonne(CON1, CON2, "FK",
							"SELECT C.CONSTRAINT_NAME, A.COLUMN_NAME FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME =  '" + nmtab1 + "'  AND C.CONSTRAINT_TYPE = 'R' order by A.CONSTRAINT_NAME, A.POSITION", nmtab1, 
							"SELECT C.CONSTRAINT_NAME, A.COLUMN_NAME FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME =  '" + nmtab2 + "'  AND C.CONSTRAINT_TYPE = 'R' order by A.CONSTRAINT_NAME, A.POSITION", nmtab2);
				} else break;
				
				if (!msg.isEmpty()) break;
				
			}

			rstabs1.close();
			rstabs2.close();
			seltabs1.close();
			seltabs2.close();

			// Confronta SEQUENZE
			if (msg.isEmpty()) {
				msg = CompareSchema.confrontaColonne(CON1, CON2, "SEQUENZA",
						"select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY from USER_SEQUENCES order by SEQUENCE_NAME", args[4], 
						"select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY from USER_SEQUENCES order by SEQUENCE_NAME", args[9]);
			}
			
		} finally {
			// chiude connessione al DB
			CON1.close();
			CON2.close();
			System.out.println("Engine: "  + args[0] + " closed connections....");
		} 

		return msg;
	}
	
	/**
	 * Confronta le colonne delle 2 query settando messaggio e valutazione
	 */
	private static String confrontaColonne(Connection CON1, Connection CON2, String confronto, String colq1,
				String nmtab1, String colq2, String nmtab2) throws Exception {
		
		// Errore della valutazione
		String msg = "";

//		if (VALUTAZIONE == false) return;

		ResultSet rsclms1 = null;
		ResultSet rsclms2 = null;
		
		Statement st1 = null;
		Statement st2 = null;
		
		try {
			
			//
			st1 = CON1.createStatement();
			st2 = CON2.createStatement();
			
			rsclms1 = st1.executeQuery(colq1);
			rsclms2 = st2.executeQuery(colq2);

			ResultSetMetaData rsd1 = rsclms1.getMetaData();
			ResultSetMetaData rsd2 = rsclms2.getMetaData();

			if (rsd1.getColumnCount() == rsd2.getColumnCount()) {

				boolean errorFound = false;
				while (rsclms1.next()  && !errorFound ) {

					rsclms2.next();

					for (int i = 1; i <= rsd1.getColumnCount(); i++) {

						String c1 = rsclms1.getString(i);
						String c2 = rsclms2.getString(i);

						if ((c1 == null && c2 == null)
								|| (c1 != null && c2 != null && c1.equals(c2))) {
							// colonne corrispondono
						} else {
							msg = "Mismatch su " + confronto + " tra " + nmtab1
									+ "." + rsd1.getColumnName(i) + ": " + c1
									+ " e " + nmtab2 + "." + rsd2.getColumnName(i)
									+ ": " + c2;
							errorFound = true;
							break;
						}
					}
				}

			} else {
				msg = "Mismatch su " + confronto + " tra numero colonne di "
						+ nmtab1 + " = " + rsd1.getColumnCount() + " e " + nmtab2
						+ " = " + rsd2.getColumnCount();
			}
			
		} catch (Exception e) {
			throw e;
		} finally {
			st1.close();
			st2.close();
			rsclms1.close();
			rsclms2.close();
		}
		return msg;
	}

	/**
	 * @return the mSG
	 */
//	public static String getMSG() {
//		return MSG;
//	}
	
	/**
	 * @return
	 * @throws Exception
	 */
	public static String confrontaMySQL(String[] args) throws Exception {

		String msg = "";

		Connection CON1 = null;
		Connection CON2 = null;
		
		try {
		
		Class.forName("com.mysql.jdbc.Driver");
		CON1 = DriverManager.getConnection("jdbc:mysql://"
				+ args[1] + ":" + args[2] + "/information_schema",
				args[4], args[5]);
		CON2 = DriverManager.getConnection("jdbc:mysql://"
				+ args[6] + ":" + args[7] + "/information_schema",
				args[9], args[10]);

		String selTabelle1 = "SELECT T.TABLE_NAME FROM TABLES T WHERE T.TABLE_SCHEMA = '" + args[3] + "' order by TABLE_NAME";
		String selTabelle2 = "SELECT T.TABLE_NAME FROM TABLES T WHERE T.TABLE_SCHEMA = '" + args[8] + "' order by TABLE_NAME";
		String selColonne = "SELECT C.COLUMN_NAME as COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.IS_NULLABLE, C.COLUMN_DEFAULT FROM COLUMNS C WHERE C.TABLE_SCHEMA = '";
//		selnColonne = "SELECT count(*) FROM COLUMNS C WHERE C.TABLE_SCHEMA = '";

		System.out.println("Engine: "  + args[0] + " compare beetwen " 
				+ args[1] + "/" + args[3] +  " e " + args[6] + "/" + args[8]);

		// setto autocommit
		CON1.setAutoCommit(true);
		CON2.setAutoCommit(true);

		Statement seltabs1 = CON1.createStatement();
		Statement seltabs2 = CON2.createStatement();
		

		// Confronta le tabelle
		ResultSet rstabs1 = seltabs1.executeQuery(selTabelle1);
		ResultSet rstabs2 = seltabs2.executeQuery(selTabelle2);

		// Scorro le tabelle dello schema
		while (rstabs1.next()) {

			rstabs2.next();

			String nmtab1 = rstabs1.getString(1);
			String nmtab2 = rstabs2.getString(1);

			if (!nmtab1.equals(nmtab2)) {
				msg = "Expected " + nmtab1
						+ " on SCHEMA1 found instead on SCHEMA2 "
						+ nmtab2;
//				VALUTAZIONE = false;
				break;
			}

			String colq1 = selColonne + args[3] + "' and C.TABLE_NAME = '" + nmtab1
						+ "' order by COLUMN_NAME";
			String colq2 = selColonne + args[8] + "' and C.TABLE_NAME = '" + nmtab2
						+ "' order by COLUMN_NAME";

			// Confronta colonne di ciascuna tabella
			msg = CompareSchema.confrontaColonne(CON1, CON2, "colonne",colq1, nmtab1, colq2,
						nmtab2);
			
			if (msg.isEmpty()) {
				// Confronta PK di ciascuna tabella
				msg = CompareSchema.confrontaColonne(CON1, CON2, "PK",
						"SELECT k.column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING(constraint_name,table_schema,table_name) WHERE t.constraint_type='PRIMARY KEY' AND t.table_schema='" + args[3] + "' AND t.table_name='" + nmtab1 + "' order by k.ORDINAL_POSITION", nmtab1, 
						"SELECT k.column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING(constraint_name,table_schema,table_name) WHERE t.constraint_type='PRIMARY KEY' AND t.table_schema='" + args[8] + "' AND t.table_name='" + nmtab2 + "' order by k.ORDINAL_POSITION", nmtab2);
				
			} else break;
			
			if (msg.isEmpty()) {
				// Confronta FK di ciascuna tabella
				msg = CompareSchema.confrontaColonne(CON1, CON2, "FK",
						"SELECT k.CONSTRAINT_NAME, k.COLUMN_NAME, k.ORDINAL_POSITION, k.REFERENCED_COLUMN_NAME FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING(constraint_name,table_schema,table_name) WHERE t.constraint_type='FOREIGN KEY' AND t.table_schema='" + args[3] + "' AND t.table_name='" + nmtab1 + "' order by k.CONSTRAINT_NAME, k.ORDINAL_POSITION", nmtab1, 
						"SELECT k.CONSTRAINT_NAME, k.COLUMN_NAME, k.ORDINAL_POSITION, k.REFERENCED_COLUMN_NAME FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING(constraint_name,table_schema,table_name) WHERE t.constraint_type='FOREIGN KEY' AND t.table_schema='" + args[8] + "' AND t.table_name='" + nmtab2 + "' order by k.CONSTRAINT_NAME, k.ORDINAL_POSITION", nmtab2);
			} else break;

			if (!msg.isEmpty()) break;
		}

		rstabs1.close();
		rstabs2.close();
		

		} finally {
			// chiude connessione al DB
			CON1.close();
			CON2.close();
			System.out.println("Engine: "  + args[0] + " closed connections....");
		} 
		
		return msg;
	}

}
